# coding:utf-8
"""
Author: r.xw
Date: 2023/12/31
File: test.py
"""
import logging

logging.info('hello')

#
# from flask import Flask
# from select import select
# from sqlalchemy import create_engine, Column, Integer, String, Table, Select, or_, and_, func
# from sqlalchemy.orm import sessionmaker, scoped_session, declarative_base
#
# app = Flask(__name__)
#
# # 创建引擎，目的是连接到数据库
# engine = create_engine('mysql+pymysql://root:123456@192.168.31.129/mumushouji', echo=True)
# # 打开一个会话
# session = sessionmaker(engine)
# # 保证线程安全
# db_session = scoped_session(session)
# # 获取基类
# base = declarative_base()
#
#
# # 创建表
# class UserBuilder(base):
#     __tablename__ = 'user_test'
#     user_id = Column(Integer, primary_key=True)
#     username = Column(String(255))
#
#
# UserBuilder.metadata.create_all(engine)
#
#
# class ArticleBuilder(base):
#     __tablename__ = 'article'
#     article_id = Column(Integer, primary_key=True)
#     article_title = Column(String(255))
#     user_id = Column(Integer)
#
#
# ArticleBuilder.metadata.create_all(engine)
#
#
# # 模型
# class UserModel(base):
#     # 表结构的反射加载
#     __table__ = Table("user", base.metadata, autoload_with=engine)
#
#
# # 取出所有记录
# rows = db_session.query(UserModel).all()
# for row in rows:
#     print(row.user_name)
# # 取出第一条记录
# first_row = db_session.query(UserModel).first()
# print(first_row.user_name)
# # 条件, filter参数是比较，==,>,<,>=,<=
# row = db_session.query(UserModel).filter(UserModel.user_name == 'jack').first()
# print(row.user_name)
# # 另一种filter写法
# row = db_session.query(UserModel).filter_by(user_name='jack').first()
# print(row.user_name)
# # 指定字段
# row = db_session.query(UserModel.user_name, UserModel.birthday).first()
# print(row.user_name, row.birthday)
#
# # 插入
# data = {
#     'user_name': 'jack',
#     'birthday': '2023-02-04'
# }
# # user = UserModel(**data)
# # db_session.add(user)
# # db_session.commit()
#
# # 更新
# # row = db_session.query(UserModel).filter_by(user_id=1).first()
# # row.user_name = 'jack2'
# # db_session.commit()
#
# # 数据删除
# # row = db_session.query(UserModel).filter_by(user_id=4).delete()
# # db_session.commit()
#
# # 另一种查询方式
# sql = Select(UserModel).filter_by(user_id=1)
# print(sql)
# row = db_session.execute(sql).scalar_one()
# print(row.user_name)
#
# # or
# rows = db_session.query(UserModel).filter(or_(UserModel.user_id == 1, UserModel.user_id == 2)).all()
# print(rows[1].user_name, rows[0].user_name)
# # and
# rows = db_session.query(UserModel).filter(
#     and_(UserModel.user_id >= 1, UserModel.user_id <= 9, or_(UserModel.user_name == 'jack'))).all()
# print(rows[2].user_name, rows[1].user_name, rows[0].user_name)
#
# # limit
# rows = db_session.query(UserModel).filter(UserModel.user_id >= 1).limit(4).all()
# print(len(rows))
# rows = db_session.query(UserModel).filter(UserModel.user_id >= 1).offset(2).limit(3).all()
# print(rows[0].user_id)
#
# # order_by
# rows = db_session.query(UserModel).order_by(UserModel.user_name.desc(), UserModel.user_id.desc()).all()
# print(rows[0].user_id)
#
# # like
# rows = db_session.query(UserModel).filter(UserModel.user_name.like('%a%')).all()
# print(len(rows))
#
# # sum
# res = db_session.query(func.sum(UserModel.user_id)).first()
# print(res)
#
#
# class ArticleModel(base):
#     __table__ = Table('article', base.metadata, autoload_with=engine)
#
#
# # join
# rows = db_session.query(UserModel, ArticleModel).join(ArticleModel, UserModel.user_id == ArticleModel.user_id).all()
# for user, article in rows:
#     print(user.user_name, article.article_title)
# # join , 指定字段
# rows = db_session.query(UserModel.user_name, ArticleModel.article_title).join(ArticleModel,
#                                                                               UserModel.user_id == ArticleModel.user_id).all()
# for name, title in rows:
#     print(name, title)
# # join , 一个模型，一个指定字段
# rows = db_session.query(UserModel, ArticleModel.article_title).join(ArticleModel,
#                                                                     UserModel.user_id == ArticleModel.user_id).all()
# for user, title in rows:
#     print(user, title)
#
# # left outer join
# rows = db_session.query(UserModel.user_name, ArticleModel.article_title).outerjoin(ArticleModel,
#                                                                                    UserModel.user_id == ArticleModel.user_id).all()
# for name, title in rows:
#     print(name, title)
